package ru.niir.goodfil.applications.wv;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import ru.niir.goodfil.audit.util.Assert;
import ru.niir.goodfil.db.Good;
import ru.niir.goodfil.db.GoodfilFactory;
import ru.niir.goodfil.db.handlers.Goodfil;
import ru.niir.goodfil.exceptions.ValidationException;

import javax.accessibility.AccessibleStateSet;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

public class WeightAndVolumeImportApplication {
    public static void main(String[] args) throws IOException {
        List<ExcelRegion> regions = new ArrayList<ExcelRegion>();
        regions.add(new ExcelRegion() {{
            setFirstLine(2);
            setLastLine(209);
            setCodeColumns(new int[] {2, 3});
            setSheetIndex(1);
            setVolumeColumn(11);
            setWeightColumn(12);
        }});
        regions.add(new ExcelRegion() {{
            setFirstLine(2);
            setLastLine(147);
            setCodeColumns(new int[] {2, 3});
            setSheetIndex(2);
            setVolumeColumn(10);
            setWeightColumn(11);
        }});
        regions.add(new ExcelRegion() {{
            setFirstLine(2);
            setLastLine(88);
            setCodeColumns(new int[] {2, 3});
            setSheetIndex(3);
            setVolumeColumn(10);
            setWeightColumn(11);
        }});
        regions.add(new ExcelRegion() {{
            setFirstLine(2);
            setLastLine(102);
            setCodeColumns(new int[] {2, 3});
            setSheetIndex(4);
            setVolumeColumn(10);
            setWeightColumn(11);
        }});
        regions.add(new ExcelRegion() {{
            setFirstLine(2);
            setLastLine(35);
            setCodeColumns(new int[] {2});
            setSheetIndex(5);
            setVolumeColumn(10);
            setWeightColumn(11);
        }});
        regions.add(new ExcelRegion() {{
            setFirstLine(2);
            setLastLine(56);
            setCodeColumns(new int[] {2});
            setSheetIndex(6);
            setVolumeColumn(9);
            setWeightColumn(10);
        }});

        LoadFromExcelFileResult r1 = loadFromExcelFile("C:\\1.xls", regions);
        printLoadFromExcelFileResult(r1);
        WeightAndVolumeValidationResult r2 = validateContainer(r1.getContainer());
        printWeightAndVolumeValidationResult(r2);
        WeightAndVolumeImportResult r3 = importContainer(r1.getContainer());
        printWeightAndVolumeImportResult(r3);
        PostImportValidationResult r4 = postImportValidation();
        printPostImportValidationResult(r4);
    }

    private static void printLoadFromExcelFileResult(LoadFromExcelFileResult r) {
        Assert.notNull(r);

        System.out.println("LoadFromExcelFileResult");
        System.out.println("    Errors (" + r.getErrors().size() + "): ");
        for (String error : r.getErrors()) {
            System.out.println("        " + error);
        }
        System.out.println("    Warnings (" + r.getWarnings().size() + "): ");
        for (String warning : r.getWarnings()) {
            System.out.println("        " + warning);
        }
        System.out.println("    Items: " + r.getContainer().getItems().size());
    }

    private static void printWeightAndVolumeValidationResult(WeightAndVolumeValidationResult r) {
        System.out.println("WeightAndVolumeValidationResult");
        System.out.println("    Code duplications (" + r.getCodeDuplications().size() + "): ");
        for (String code : r.getCodeDuplications()) {
            System.out.println("        " + code);
        }
        System.out.println("    GoodsWithoutVolume (" + r.getGoodsWithoutVolume().size() + "): ");
        for (String code : r.getGoodsWithoutVolume()) {
            System.out.println("        " + code);
        }
        System.out.println("    GoodsWithoutWeight (" + r.getGoodsWithoutWeight().size() + "): ");
        for (String code : r.getGoodsWithoutWeight()) {
            System.out.println("        " + code);
        }
        System.out.println("    MissingCodes (" + r.getMissingCodes().size() + "): ");
        for (String code : r.getMissingCodes()) {
            System.out.println("        " + code);
        }
    }

    private static void printWeightAndVolumeImportResult(WeightAndVolumeImportResult r) {
        System.out.println("WeightAndVolumeImportResult");
        System.out.println("Processed: " + r.getProcessed());
        System.out.println("Updated: " + r.getUpdated());
    }

    private static void printPostImportValidationResult(PostImportValidationResult r) {
        System.out.println("PostImportValidationResult");
        System.out.println("    GoodsWithoutVolume (" + r.getGoodsWithoutVolume().size() + "):");
        for (Good good : r.getGoodsWithoutVolume()) {
            System.out.println("        " + good);
        }
        System.out.println("    GoodsWithoutWeight (" + r.getGoodsWithoutWeight().size() + "):");
        for (Good good : r.getGoodsWithoutWeight()) {
            System.out.println("        " + good);
        }
    }

    public static PostImportValidationResult postImportValidation() {
        PostImportValidationResult result = new PostImportValidationResult();

        Goodfil gf = GoodfilFactory.getInstance();
        final List<Good> allGoods = gf.getGoods(0, Integer.MAX_VALUE, null);
        for (Good good : allGoods) {
            if (good.isDeleted()) continue;

            if (good.getWeight() <= 0) {
                result.addGoodWithoutWeight(good);
            }
            if (good.getVolume() <= 0) {
                result.addGoodWithoutVolume(good);
            }
        }
        return result;
    }

    public static WeightAndVolumeImportResult importContainer(GoodVolumeAndWeightContainer container) {
        Assert.notNull(container);

        Goodfil gf = GoodfilFactory.getInstance();
        final List<Good> allGoods = gf.getGoods(0, Integer.MAX_VALUE, null);
        final List<Good> goodsForUpdate = new ArrayList<Good>();

        int processed = 0;
        int updated = 0;
        for (GoodVolumeAndWeight item : container.getItems()) {
            for (Good good : allGoods) {
                if (maskCode(item.getCode()).equals(maskCode(good.getCode()))) {
                    boolean hasChanges = false;

                    if (item.getWeight() != null && !item.getWeight().equals(new BigDecimal(good.getWeight()))) {
                        good.setWeight(item.getWeight().floatValue());
                        hasChanges = true;
                    }

                    if (item.getVolume() != null && !item.getVolume().equals(new BigDecimal(good.getVolume()))) {
                        good.setVolume(item.getVolume().floatValue());
                        hasChanges = true;
                    }

                    if (hasChanges) {
                        goodsForUpdate.add(good);
                        updated++;
                        break;
                    }
                }
                processed++;
            }
        }

        try {
            gf.updateGoods(goodsForUpdate);
        } catch (ValidationException e) {
            e.printStackTrace();
        }

        return new WeightAndVolumeImportResult(processed, updated);
    }

    public static WeightAndVolumeValidationResult validateContainer(GoodVolumeAndWeightContainer container) {
        Assert.notNull(container);

        WeightAndVolumeValidationResult result = new WeightAndVolumeValidationResult();
        Set<String> uniqueCodes = new HashSet<String>();
        for (GoodVolumeAndWeight item : container.getItems()) {
            if (uniqueCodes.contains(item.getCode())) {
                result.addCodeDuplication(item.getCode());
            } else {
                uniqueCodes.add(item.getCode());
            }

            if (item.getVolume() == null) {
                result.addGoodWithoutVolume(item.getCode());
            }
            if (item.getWeight() == null) {
                result.addGoodWithoutWeight(item.getCode());
            }
        }

        Goodfil gf = GoodfilFactory.getInstance();
        List<Good> allGoods = gf.getGoods(0, Integer.MAX_VALUE, null);
        for (GoodVolumeAndWeight item : container.getItems()) {
            boolean goodFound = false;
            for (Good good : allGoods) {
                if (maskCode(item.getCode()).toUpperCase().equals(maskCode(good.getCode().toUpperCase()))) {
                    goodFound = true;
                }
            }

            if (!goodFound) {
                result.addMissingCode(maskCode(item.getCode()).toUpperCase());
            }
        }

        return result;
    }

    public static String maskCode(String code) {
        return code.toUpperCase().replace(" ", "").trim();
    }

    public static LoadFromExcelFileResult loadFromExcelFile(String filename, Collection<ExcelRegion> regions) throws IOException {
        Assert.notNull(filename);
        Assert.notNull(regions);

        LoadFromExcelFileResult result = new LoadFromExcelFileResult();
        GoodVolumeAndWeightContainer container = new GoodVolumeAndWeightContainer();

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filename));
        for (ExcelRegion region : regions) {
            HSSFSheet sheet = workbook.getSheetAt(region.sheetIndex - 1);

            for (int i = region.firstLine - 1; i < region.lastLine - 1; i++) {
                HSSFRow row = sheet.getRow(i);

                String code = "";
                for (int codeColumn : region.codeColumns) {
                    HSSFCell cell = row.getCell(codeColumn - 1);
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                        code += bd.toPlainString();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        code += cell.getStringCellValue();
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        continue;
                    } else {
                        result.error("Cannot proceed cell (" + cell.getRowIndex() +", " + cell.getColumnIndex() + ") at region " + region + ": unknown cell type");
                    }
                }
                if (StringUtils.isBlank(code)) {
                    result.warn("Line " + String.valueOf(i + 1) + " has no good code");
                    continue;
                }

                BigDecimal bdWeight = null;
                Cell weightCell = row.getCell(region.weightColumn - 1);
                if (weightCell == null) {
                    result.error("Region " + region + " is invalid");
                    break;
                } else {
                    if (weightCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        bdWeight = new BigDecimal(weightCell.getNumericCellValue());
                    } else if (weightCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        bdWeight = new BigDecimal(weightCell.getNumericCellValue());
                    } else if (weightCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        String weight = weightCell.getStringCellValue();
                        if (!StringUtils.isBlank(weight)) {
                            bdWeight = new BigDecimal(weightCell.getStringCellValue());
                        }
                    } else if (weightCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        bdWeight = null;
                    } else {
                        result.error("Cannot proceed cell (" + weightCell.getRowIndex() +", " + weightCell.getColumnIndex() + ") at region " + region + ": unknown cell type");
                    }

                    BigDecimal bdVolume = null;
                    Cell volumeCell = row.getCell(region.weightColumn - 1);
                    if (volumeCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                        bdVolume = new BigDecimal(volumeCell.getNumericCellValue());
                    } else if (volumeCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        bdVolume = new BigDecimal(volumeCell.getNumericCellValue());
                    } else if (volumeCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        String weight = volumeCell.getStringCellValue();
                        if (!StringUtils.isBlank(weight)) {
                            bdVolume = new BigDecimal(volumeCell.getStringCellValue());
                        }
                    } else if (volumeCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                        bdVolume = null;
                    } else {
                        result.error("Cannot proceed cell (" + weightCell.getRowIndex() +", " + weightCell.getColumnIndex() + "): unknown cell type");
                    }

                    if (bdWeight != null) {
                        bdWeight = bdWeight.setScale(5, BigDecimal.ROUND_HALF_UP);
                    } else {
                        result.warn("Null weight for good: " + code);
                    }

                    if (bdVolume != null) {
                        bdVolume = bdVolume.setScale(5, BigDecimal.ROUND_HALF_UP);
                    } else {
                        result.warn("Null volume for good: " + code);
                    }

                    container.addItem(new GoodVolumeAndWeight(code, bdWeight, bdVolume));
                }
            }
        }

        result.setContainer(container);
        return result;
    }

    public static class WeightAndVolumeValidationResult {
        private final Set<String> codeDuplications = new LinkedHashSet<String>();
        private final Set<String> goodsWithoutWeight = new LinkedHashSet<String>();
        private final Set<String> goodsWithoutVolume = new LinkedHashSet<String>();
        private final Set<String> missingCodes = new HashSet<String>();

        public void addCodeDuplication(String codeDuplication) {
            Assert.notBlank(codeDuplication);
            codeDuplications.add(codeDuplication);
        }

        public void addGoodWithoutWeight(String code) {
            Assert.notBlank(code);
            goodsWithoutWeight.add(code);
        }

        public void addGoodWithoutVolume(String code) {
            Assert.notBlank(code);
            goodsWithoutVolume.add(code);
        }

        public void addMissingCode(String code) {
            Assert.notBlank(code);
            missingCodes.add(code);
        }

        public Set<String> getCodeDuplications() {
            return codeDuplications;
        }

        public Set<String> getGoodsWithoutWeight() {
            return goodsWithoutWeight;
        }

        public Set<String> getGoodsWithoutVolume() {
            return goodsWithoutVolume;
        }

        public Set<String> getMissingCodes() {
            return missingCodes;
        }
    }

    public static class PostImportValidationResult {
        final List<Good> goodsWithoutWeight = new ArrayList<Good>();
        final List<Good> goodsWithoutVolume = new ArrayList<Good>();

        public void addGoodWithoutWeight(Good good) {
            Assert.notNull(good);
            Assert.isTrue(good.getWeight() <= 0);

            goodsWithoutWeight.add(good);
        }

        public void addGoodWithoutVolume(Good good) {
            Assert.notNull(good);
            Assert.isTrue(good.getVolume() <= 0);

            goodsWithoutVolume.add(good);
        }

        public List<Good> getGoodsWithoutWeight() {
            return goodsWithoutWeight;
        }

        public List<Good> getGoodsWithoutVolume() {
            return goodsWithoutVolume;
        }
    }

    public static class WeightAndVolumeImportResult {
        private int processed;
        private int updated;

        public WeightAndVolumeImportResult() {
        }

        public WeightAndVolumeImportResult(int processed, int updated) {
            this.processed = processed;
            this.updated = updated;
        }

        public int getProcessed() {
            return processed;
        }

        public void setProcessed(int processed) {
            this.processed = processed;
        }

        public int getUpdated() {
            return updated;
        }

        public void setUpdated(int updated) {
            this.updated = updated;
        }
    }

    public static class LoadFromExcelFileResult {
        private final List<String> errors = new ArrayList<String>();
        private final List<String> warnings = new ArrayList<String>();
        private GoodVolumeAndWeightContainer container;

        public boolean hasErrors() {
            return errors.size() > 0;
        }

        public boolean hasWarnings() {
            return warnings.size() > 0;
        }

        public void warn(String warn) {
            Assert.notNull(warn);
            warnings.add(warn);
        }

        public void error(String error) {
            Assert.notNull(error);
            warnings.add(error);
        }

        public GoodVolumeAndWeightContainer getContainer() {
            return container;
        }

        public void setContainer(GoodVolumeAndWeightContainer container) {
            this.container = container;
        }

        public List<String> getErrors() {
            return errors;
        }

        public List<String> getWarnings() {
            return warnings;
        }
    }

    public static class GoodVolumeAndWeightContainer {
        private final List<GoodVolumeAndWeight> items = new ArrayList<GoodVolumeAndWeight>();

        public void addItem(GoodVolumeAndWeight item) {
            Assert.notNull(item);
            items.add(item);
        }

        public List<GoodVolumeAndWeight> getItems() {
            return items;
        }
    }

    public static class GoodVolumeAndWeight {
        private String code;
        private BigDecimal volume;
        private BigDecimal weight;

        public GoodVolumeAndWeight(String code, BigDecimal volume, BigDecimal weight) {
            Assert.notNull(code);
            Assert.notBlank(code);

            this.code = code;
            this.volume = volume;
            this.weight = weight;
        }

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            Assert.notNull(code);
            Assert.notBlank(code);

            this.code = code;
        }

        public BigDecimal getVolume() {
            return volume;
        }

        public void setVolume(BigDecimal volume) {
            this.volume = volume;
        }

        public BigDecimal getWeight() {
            return weight;
        }

        public void setWeight(BigDecimal weight) {
            this.weight = weight;
        }

        @Override
        public String toString() {
            return code + " вес: " + String.valueOf(weight) + " объем: " + String.valueOf(volume);
        }
    }

    public static class ExcelRegion {
        private int sheetIndex;
        private int firstLine;
        private int lastLine;
        private int[] codeColumns;
        private int weightColumn;
        private int volumeColumn;

        @Override
        public String toString() {
            return "ExcelRegion{" +
                    "sheetIndex=" + sheetIndex +
                    ", firstLine=" + firstLine +
                    ", lastLine=" + lastLine +
                    ", codeColumns=" + StringUtils.join(Arrays.asList(codeColumns), ",") +
                    ", weightColumn=" + weightColumn +
                    ", volumeColumn=" + volumeColumn +
                    '}';
        }

        public int getSheetIndex() {
            return sheetIndex;
        }

        public void setSheetIndex(int sheetIndex) {
            this.sheetIndex = sheetIndex;
        }

        public int getFirstLine() {
            return firstLine;
        }

        public void setFirstLine(int firstLine) {
            this.firstLine = firstLine;
        }

        public int getLastLine() {
            return lastLine;
        }

        public void setLastLine(int lastLine) {
            this.lastLine = lastLine;
        }

        public int[] getCodeColumns() {
            return codeColumns;
        }

        public void setCodeColumns(int[] codeColumns) {
            this.codeColumns = codeColumns;
        }

        public int getWeightColumn() {
            return weightColumn;
        }

        public void setWeightColumn(int weightColumn) {
            this.weightColumn = weightColumn;
        }

        public int getVolumeColumn() {
            return volumeColumn;
        }

        public void setVolumeColumn(int volumeColumn) {
            this.volumeColumn = volumeColumn;
        }
    }
}
